#!/usr/bin/env python
# coding=utf-8
from include import para
import mysql.connector


class dataBaseConnect:
    def __init__(self,configPath):
        config = para.paraConfig(configPath)
        self.cnx = mysql.connector.connect(**config)
        self.curson = self.cnx.cursor()
        
    def show(self,tablename):
        ''' 返回值为0说明该表不存在 '''
        sql = "show tables like '" + tablename + "'"
        self.curson.execute(sql)
        result = self.curson.fetchall()
        return len(result)

    def sqlCheck(self,type,sql):
        if type.lower() not in sql.lower():
            print("不是" + type + "语句:" + sql)
            return -1
        return 0

    def create(self,tablename,sql):
        ''' 返回-1表示建表异常失败,返回1表示已存在，返回0表示成功 '''
        if self.sqlCheck("create",sql) != 0:
            return -1

        if not self.show(tablename):
            print(sql)
            self.curson.execute(sql)
            result = len(self.curson.fetchall())
            self.commit()
            print(tablename + " 建表成功")
        else:
            result = 1
            print(tablename + " 表已存在")
        return result

    def insert(self,tablename,sql):
        ''' 返回-1表示插入失败 
            注意：须在函数外调用commit'''
        if self.sqlCheck("insert",sql) != 0:
            return -1

        #print(sql)
        if self.show(tablename):
            self.curson.execute(sql)
            result = len(self.curson.fetchall())
        else:
            print("插入出错！表" + tablename + "不存在")
            result = -1
        return result

    def select(self,tablename,sql):
        ''' 返回None表示查找失败 '''
                
        if self.sqlCheck("select",sql) != 0:
            return None

        #print(sql)
        if self.show(tablename):
            self.curson.execute(sql)
            result = self.curson.fetchall()
        else:
            print("查找出错！表" + tablename + "不存在")
            result = None
        return result

    def selectMax(self,tablename,item):
        ''' 查找某表中，某项的最大值 '''
        sql = "select max(`{}`) from `{}`;".format(item,tablename)
        if self.show(tablename):
            self.curson.execute(sql)
            result = self.curson.fetchone()[0]
        else:
            print("表" + tablename + "不存在")
            result = None
        return result

    def selectMin(self,tablename,item):
        ''' 查找某表中，某项的最小值 '''
        sql = "select min(`{}`) from `{}`;".format(item,tablename)
        if self.show(tablename):
            self.curson.execute(sql)
            result = self.curson.fetchone()[0]
        else:
            print("表" + tablename + "不存在")
            result = None
        return result

    def update(self,tablename,sql):
        ''' 返回-1表示插入失败 
            注意：须在函数外调用commit'''
        if self.sqlCheck("update",sql) != 0:
                return -1

        #print(sql)
        if self.show(tablename):
            self.curson.execute(sql)
            result = self.curson.fetchall()
            return result
        else:
            print("插入出错！表" + tablename + "不存在")
            return -1

    def commit(self):
        self.cnx.commit()


